# -*- encoding:utf-8 -*-
import openpyxl
import os
import re
from pathlib import Path

from openpyxl import Workbook
from openpyxl.drawing.image import Image
from openpyxl.utils import get_column_letter
from PIL import Image as PILImage


def what_grade(str):
    # KGA
    # ECC3B
    # G3B
    # G6-12
    kg = '(^KG)([A-Z]+)'
    ecc = '(^ECC)(\d+)([A-Z]+)'
    primary = '(^G)(\d+)([A-Z]+)'
    middle = '(^G)([1-9]+)'
    str_re = str.strip()
    if re.search(kg,str_re):
        res = re.match(kg, str_re)
        if res:
            gr_type = res.group(1)   #KG
            group = res.group(2) #A
            str1 = '小学部[EN:ES]/'
            str_f = str1 + gr_type + "/" + "2324" + str_re
            return str_f
    if re.search(ecc,str_re):
        res = re.match(ecc, str_re)
        if res:
            gr_type = res.group(1)   #ECC
            year = res.group(2) #2
            group = res.group(3) #A
            str1 = '幼儿园[EN:ECC]/'
            str_f = str1 + gr_type + year + "/" + "2324" + str_re
            return str_f

    if re.search("^G[1-5][A-Z]", str_re):
        res = re.match(primary, str_re)
        #print(res)
        if res:
            gr_type = res.group(1)  # G
            year = res.group(2)  # 3
            group = res.group(3)  # B
            str1 = '小学部[EN:ES]/'
            str_f = str1 + gr_type + year + "/" + "2324" + str_re
            return str_f

    if re.search("^G[1-9]+", str_re):
        res = re.match(middle, str_re)
        # print(res.group(2))
        if res:
            str1 = '中学部[EN:MSHS]/'
            str_f = str1 + "2324" + str_re
            return str_f



# collect-stu-info-excel
workbook1 = openpyxl.load_workbook('C:\\Users\\VV\\Desktop\\5555\\combo-ex1.xlsx')
sheet1 = workbook1['Sheet1']

#output excel
workbook2 = openpyxl.load_workbook('C:\\Users\\VV\\Desktop\\5555\\export.xlsx')
sheet2 = workbook2['Sheet1']

combo_stu_obj = {}
inner_list_info = []

for row in sheet1.iter_rows(min_row=1, min_col=1, max_row=2753, max_col=3):
    cell_value_grade = row[0].value
    cell_value_name = row[1].value
    cell_value_tel = row[2].value
    inner_obj = {}
    inner_obj[cell_value_name] = cell_value_grade    #生成单个学生的对象


    if cell_value_tel in combo_stu_obj:   #先判断电话号是否在对象中如果对象中没有直接添加学生对象，如果对象中有电话号，那么就需要判断是否为list如果是则取出list然后追加，如果不是则有可能是单个对象则往templist中追加老数据和新数据
        temp_list = []
        temp_v = combo_stu_obj[cell_value_tel]
        if isinstance(temp_v,list):

            temp_v.append(inner_obj)
            combo_stu_obj[cell_value_tel] = temp_v
        else:

            temp_list.append(temp_v)
            temp_list.append(inner_obj)
            combo_stu_obj[cell_value_tel] = temp_list
    else:

        combo_stu_obj[cell_value_tel] = inner_obj

print(combo_stu_obj)
step = 10
for key in combo_stu_obj.keys():
    par_tel_num = key    #key是对象取的电话号码
    sheet2.cell(row=step, column=2).value = par_tel_num  #电话号码写入第二列
    sheet2.cell(row=step, column=7).value = par_tel_num  # 电话号码写入第七列


    if isinstance(combo_stu_obj[key], list):   #如果是列表证明一个号码有多个孩子
        str = "海嘉学校[EN:BIBA]/海嘉大社区[EN:BIBA COMMUNITY]/"
        final_str = ""
        for n in combo_stu_obj[key]:

            for k in n:
                keyn = k   #学生名字
                value = what_grade(n[k])  #学生年级/部门
                kid_path = str + value +";"
                sheet2.cell(row=step, column=1).value = keyn
                final_str = final_str + kid_path

        sheet2.cell(row=step, column=5).value = final_str

    if isinstance(combo_stu_obj[key], dict):  #如果是对象就证明只有一个孩子
        str = "海嘉学校[EN:BIBA]/海嘉大社区[EN:BIBA COMMUNITY]/"
        final_str = ""
        for n in combo_stu_obj[key].keys():
            keyn = n  #学生名字
            value = what_grade(combo_stu_obj[key][n])  #学生年级/部门
            kid_path = str + value + ";"
            sheet2.cell(row=step, column=1).value = keyn
            final_str = final_str + kid_path
        sheet2.cell(row=step, column=5).value = final_str
    step = step +1


workbook2.save('C:\\Users\\VV\\Desktop\\5555\\export.xlsx')
workbook2.close()









